<?php

/* 
 * Manager/ExcelManager.php
 */

namespace RHX\Model\Manager;

use Zend\Db\Adapter\Adapter;

class ExcelManager{
    protected $adapter = null;
    /**
     *
     * @var \PHPExcel
     */
    protected $excel = null;
    protected $reader = null;
    protected $writer = null;
    protected $fileName = '';
    protected $dir = '';
    protected $defaultDir = '';
    /**
     * Constructeur
     * 
     * @param \Zend\Db\Adapter\Adapter $adapter
     */
    public function __construct(Adapter $adapter){
	$this->adapter = $adapter;
	$this->excel = new \PHPExcel();
    }
    
    /**
     * Adapteur de base de données
     * 
     * @return Adapter
     */
    public function getAdapter(){
	return $this->adapter;
    }
    
    /**
     * Charger un fichier Excel
     * 
     * @param string $filename
     */
    public function load($filename = 'integration.xls'){
	$pwd = getcwd();
	$dir = '/upload/excel/';
	$dirPublic = '/public/upload/excel/';
	if(file_exists($pwd.$dir.$filename)){
	    $this->openExcel($pwd.$dir.$filename);
	}elseif(file_exists($pwd.$dirPublic.$filename)){
	    $this->openExcel($pwd.$dirPublic.$filename);
	}else{
	    throw new \Exception("Le fichier '$pwd$dir$filename' ou '$pwd$dirPublic$filename' n'existe pas");
	}
    }
    
    /**
     * 
     * @param string $fullName
     */
    private function openExcel($fullName){
	$this->reader = \PHPExcel_IOFactory::createReader("Excel5");
	$this->excel = $this->reader->load($fullName);
	$this->dir = dirname($fullName);
	$this->fileName = str_replace('.xls', '1.xls', substr($fullName, strrpos($fullName, '/')));
	$this->writer = new \PHPExcel_Writer_Excel5($this->excel);
	//$this->writer->setOffice2003Compatibility(true);
	$this->defaultDir = $this->dir .$this->fileName;
    }
    
    /**
     * Sauvegarder le fichier excel
     * 
     * @param string $filename
     */
    public function save($filename = null){
	$fullPath = '';
	if(is_null($filename)){
	    $fullPath = $this->dir .'/'.$this->fileName;
	}else{
	    $fullPath = $this->dir .'/'.$filename;
	}
	$this->writer
		->save($fullPath);
    }
    
    /**
     * Enlever les caractères accentutés
     * 
     * @param string $filename
     * @return string
     */
    private function processFileName($filename){
	return $filename;
    }


    /**
     * 
     * @param array $instructions
     */
    public function fillExcel($instructions){
	$i = 7;
	foreach($instructions as $instruction){
	    $this->fillOne($instruction, $i++);
	}
    }
    
    /**
     * Remplissage des informations de synthèse
     * 
     * @param array $instructions
     */
    public function fillSynthese($instructions){
	$i = 4;
	foreach($instructions as $instruction){
	    $this->fillSyntheseOne($instruction, $i++);
	}
    }
    
    /**
     * 
     * @param \RHX\Model\Object\InstructionPaie $instruction
     */
    private function fillOne(\RHX\Model\Object\InstructionPaie $instruction, $ligne = 7){
	$sheet = $this->excel
		->getSheet(0);
	$employe = $instruction->getEmploye();
	$sheet->setCellValue("A$ligne", $employe->nom1);
	$sheet->setCellValue("B$ligne", $employe->prenom);
	$sheet->setCellValue("C$ligne", $employe->nom2);
	$sheet->setCellValue("D$ligne", '');
	$sheet->setCellValue("E$ligne", number_format($instruction->mensualite, 2, ',', ''));
	$sheet->setCellValue("F$ligne", number_format($employe->getTaux(), 2, ',', ''));
	$sheet->setCellValue("G$ligne", number_format($instruction->heure_comptabilise, 2, ',', ''));
	$sheet->setCellValue("H$ligne", number_format($instruction->heure_comptabilise_heure_supplementaire, 2, ',', ''));
	$sheet->setCellValue("I$ligne", number_format(0, 2, ',', ''));
	$sheet->setCellValue("J$ligne", number_format($instruction->heure_supplementaire, 2, ',', ''));
	$sheet->setCellValue("K$ligne", number_format($instruction->ferie, 2, ',', ''));
	$sheet->setCellValue("L$ligne", number_format($instruction->dimanches, 2, ',', ''));
	$sheet->setCellValue("M$ligne", $instruction->heure_positive_libelle);
	$sheet->setCellValue("N$ligne", number_format($instruction->heure_negative, 2, ',', ''));
	$sheet->setCellValue("O$ligne", $instruction->heure_negative_libelle);
	$sheet->setCellValue("P$ligne", number_format($instruction->am2_heure, 2, ',', ''));
	$sheet->setCellValue("Q$ligne", $instruction->heure_positive_libelle);
	$sheet->setCellValue("R$ligne", number_format($instruction->entree_sortie_heure, 2, ',', ''));
	$sheet->setCellValue("S$ligne", number_format($instruction->cp_jour, 2, ',', ''));
	$sheet->setCellValue("T$ligne", $instruction->cp_libelle);
	$sheet->setCellValue("U$ligne", number_format($instruction->heure_module_css, 2, ',', ''));
	$sheet->setCellValue("V$ligne", $instruction->css_libelle);
	$sheet->setCellValue("W$ligne", number_format($instruction->ijss_brute_montant, 2, ',', ''));
	$sheet->setCellValue("X$ligne", $instruction->ijss_brute_libelle);
	$sheet->setCellValue("Y$ligne", number_format($instruction->prime_montant, 2, ',', ''));
	$sheet->setCellValue("Z$ligne", $instruction->prime_libelle);
	$sheet->setCellValue("AA$ligne", number_format(0, 2, ',', ''));
	$sheet->setCellValue("AB$ligne", '');
	$sheet->setCellValue("AC$ligne", number_format($instruction->hors_temps_travail_montant, 2, ',', ''));
	$sheet->setCellValue("AD$ligne", $instruction->hors_temps_travail_libelle);
	$sheet->setCellValue("AE$ligne", number_format($instruction->vehicule, 2, ',', ''));
	$sheet->setCellValue("AF$ligne", number_format($instruction->logement, 2, ',', ''));
	$sheet->setCellValue("AG$ligne", number_format($instruction->mutuelle, 2, ',', ''));
	$sheet->setCellValue("AH$ligne", number_format($instruction->repas, 2, ',', ''));
	$sheet->setCellValue("AI$ligne", number_format($instruction->base_co, 2, ',', ''));
	$sheet->setCellValue("AJ$ligne", number_format($instruction->acompte, 2, ',', ''));
	$sheet->setCellValue("AK$ligne", number_format($instruction->ijss_net, 2, ',', ''));
	$sheet->setCellValue("AL$ligne", number_format($instruction->pret, 2, ',', ''));
	$sheet->setCellValue("AM$ligne", number_format($instruction->atd, 2, ',', ''));
	$sheet->setCellValue("AN$ligne", number_format($instruction->participation, 2, ',', ''));
	$sheet->setCellValue("AO$ligne", ($instruction->changement_contrat) ? 'Oui' : '');
	$sheet->setCellValue("AP$ligne", $instruction->commentaire1);
	$sheet->setCellValue("AQ$ligne", $instruction->commentaire2);
	$sheet->setCellValue("AR$ligne", $instruction->commentaire3);
	$sheet->setCellValue("AS$ligne", $instruction->commentaire4);
    }
    
    /**
     * Remplissage Synthese 1 ligne
     * 
     * @param \RHX\Model\Object\InstructionPaie $instruction
     * @param int $ligne
     */
    private function fillSyntheseOne(\RHX\Model\Object\InstructionPaie $instruction, $ligne = 4){
	$sheet = $this->excel
		->getSheet(0);
	$employe = $instruction->getEmploye();
	$sheet->setCellValue("A$ligne", $employe->nom1);
	$sheet->setCellValue("B$ligne", $employe->prenom);
	$contratType = $employe->getContratType();
	$sheet->setCellValue("C$ligne", $contratType->abrev);
	$sheet->setCellValue("D$ligne", $employe->site_id);
	$sheet->setCellValue("E$ligne", $employe->getTaux());
	$sheet->setCellValue("F$ligne", $employe->getTauxCharge(null, true, true, 2));
	$sheet->setCellValue("G$ligne", $instruction->mensualite);
	$sheet->setCellValue("H$ligne", $instruction->heure_comptabilise);
	$sheet->setCellValue("I$ligne", $instruction->heure_comptabilise_heure_supplementaire);
	$sheet->setCellValue("J$ligne", 0.0); // absences
	$sheet->setCellValue("K$ligne", 0.0); // absences CP
	$sheet->setCellValue("L$ligne", $instruction->repas);
	$sheet->setCellValue("M$ligne", $instruction->prime_montant);
	$sheet->setCellValue("N$ligne", 0.0);
	$sheet->setCellValue("O$ligne", 0.0);
	$sheet->setCellValue("P$ligne", '');
	$sheet->setCellValue("T$ligne", '');
	$sheet->setCellValue("U$ligne", '');
	$sheet->setCellValue("W$ligne", '');
	$sheet->setCellValue("X$ligne", '');
	$sheet->setCellValue("Y$ligne", '');
	$sheet->setCellValue("Z$ligne", '');
	$this->miseEnForme($ligne);
	$this->formules($ligne);
    }
    
    /**
     * 
     * @param type $array
     */
    public function fillInformation($array){
	$sheet = $this->excel
		->getSheet(0);
	$sheet->setCellValue('B1', isset($array['site'])? $array['site'] : '');
	$sheet->setCellValue('B2', isset($array['periode']) ? $array['periode'] : '');
    }
    
    /**
     * Remplissage des titres Synthèse
     * 
     * @param array $array
     */
    public function fillSyntheseInformation($array){
	$sheet = $this->excel
		->getSheet(0);
	$sheet->setCellValue('C1', isset($array['site'])? $array['site'] : '');
	$sheet->setCellValue('C2', isset($array['periode']) ? $array['periode'] : '');
    }
    
    /**
     * Heure Total
     * 
     * @param \RHX\Model\Object\InstructionPaie $paie
     * @param real $absence
     * @param real $regulTN
     * @param real $regulSup
     * @return real
     */
    public function heureTotal(\RHX\Model\Object\InstructionPaie $paie, $absence = 0.0, $regulTN = 0.0, $regulSup = 0.0){
	return floatval($paie->mensualite) + floatval($paie->heure_comptabilise) + floatval($paie->heure_comptabilise_heure_supplementaire) + $absence + $regulSup + $regulTN;
    }
    
    /**
     * 
     * @param real $heureTotal
     * @param real $mensualite
     * @return real
     */
    public function EqTpsPlein($heureTotal, $mensualite = 151.67){
	if($mensualite == 0){
	    return 0.0;
	}
	return $heureTotal / $mensualite;
    }
    
    /**
     * 
     * @param \RHX\Model\Object\InstructionPaie $paie
     * @param real $taux
     * @param real $abs
     * @param real $regulTN
     * @param real $regulSup
     * @param real $indCP
     * @return real
     */
    public function salaireBrute(\RHX\Model\Object\InstructionPaie $paie, $taux, $abs = 0.0, $regulTN = 0.0, $regulSup = 0.0, $indCP = 0.0){
	$salaire = ((floatval($paie->mensualite) + floatval($paie->heure_comptabilise) - $abs + $regulTN) * $taux) + ((floatval($paie->heure_comptabilise_heure_supplementaire) + $regulSup) * $taux * 1.25) + floatval($paie->prime_montant) + $indCP + (floatval($paie->repas) * 3.49);
	//echo "<pre>(($paie->mensualite + $paie->heure_comptabilise - $abs + $regulTN) * $taux) + ($paie->heure_comptabilise_heure_supplementaire + $regulSup) * $taux * 1.25) + $paie->prime_montant + $indCP + ($paie->repas * 3.49)</pre>";
	return $salaire;
    }
    
    /**
     * 
     * @param real $mensualite
     * @param real $taux
     * @param real $cpN1
     * @param real $cpN
     * @param real $coef
     * @return real
     */
    public function provCP($mensualite, $taux, $cpN1, $cpN, $coef = 25){
	$div = $coef * ($cpN + $cpN1);
	if($div == 0){
	    return 0.0;
	}
	return ($mensualite * $taux) / $div;
    }
    
    /**
     * Mise en forme d'une cellule ou d'un ensemble de cellule
     * 
     * @param string $cells
     * @param string $bgColor
     * @param string $borderColor
     * @param int $borderType
     */
    private function makeUp($cells, $bgColor = 'FFFFFF', $borderColor = '000000', $borderType = \PHPExcel_Style_Border::BORDER_THIN, $textAlign = \PHPExcel_Style_Alignment::HORIZONTAL_CENTER){
	$this->setBgColor($cells, $bgColor);
	$this->setBorder($cells, $borderType, $borderColor);
    }
    
    /**
     * Définir couleur de cellule
     * 
     * @param string $cells
     * @param string $color
     */
    private function setBgColor($cells, $color = "FFFFFF"){
	$sheet = $this->excel->getActiveSheet();
	$style = $sheet->getStyle($cells);
	$style->getFill()
		->applyFromArray(array(
		    'type' => \PHPExcel_Style_Fill::FILL_SOLID,
		    'startcolor' => array('rgb' => $color),
		));
    }
    
    /**
     * 
     * @param string $cells
     * @param int $type
     * @param string $color
     */
    private function setBorder($cells, $type = \PHPExcel_Style_Border::BORDER_THIN, $color = '000000'){
	$sheet = $this->excel->getActiveSheet();
	$style = $sheet->getStyle($cells);
	$style/*->getBorders()->getAllBorders()->setBorderStyle($type)*/
		->applyFromArray(array(
		    'borders' => array(
			'outline' => array(
			    'style' => $type,
			    'color' => array('argb' => 'FF'.$color),
			),
		    ),
		));
    }
    
    /**
     * Mise en forme générale
     * 
     * @param int $ligne
     */
    private function miseEnForme($ligne){
	$bgBleu = 'CCFFFF';
	$bgGris = 'C0C0C0';
	$bgRose = 'FF99CC';
	$bgOrange = 'FFCC99';
	$this->makeUp("A$ligne", $bgBleu);
	$this->makeUp("B$ligne", $bgBleu);
	$this->makeUp("C$ligne", $bgBleu); 
	$this->makeUp("D$ligne", $bgBleu); 
	$this->makeUp("E$ligne", $bgBleu); 
	$this->makeUp("F$ligne", $bgBleu); 
	$this->makeUp("G$ligne", $bgBleu); 
	$this->makeUp("H$ligne", $bgBleu); 
	$this->makeUp("I$ligne", $bgBleu); 
	$this->makeUp("J$ligne", $bgBleu); 
	$this->makeUp("K$ligne", $bgBleu); 
	$this->makeUp("L$ligne", $bgBleu); 
	$this->makeUp("M$ligne", $bgBleu); 
	$this->makeUp("N$ligne", $bgBleu); 
	$this->makeUp("O$ligne", $bgBleu); 
	$this->makeUp("P$ligne", $bgBleu); 
	$this->makeUp("Q$ligne", $bgBleu);
	$this->makeUp("R$ligne", $bgBleu);
	$this->makeUp("S$ligne", $bgGris);
	$this->makeUp("T$ligne", $bgBleu); 
	$this->makeUp("U$ligne", $bgBleu); 
	$this->makeUp("V$ligne", $bgRose);
	$this->makeUp("W$ligne", $bgOrange); 
	$this->makeUp("X$ligne", $bgOrange); 
	$this->makeUp("Y$ligne", $bgOrange); 
	$this->makeUp("Z$ligne", $bgOrange);
	$this->setTextAlignH("C$ligne:Z$ligne");
    }
    
    /**
     * Application des formules excels
     * 
     * @param int $ligne
     */
    private function formules($ligne){
	$sheet = $this->excel->getActiveSheet();
	$this->setFormat("E$ligne:K$ligne");
	$this->setFormat("M$ligne");
	$sheet->setCellValue("Q$ligne", "=G$ligne+H$ligne+I$ligne-J$ligne+N$ligne+O$ligne");
	$this->setFormat("Q$ligne");
	$sheet->setCellValue("R$ligne", "=Q$ligne/151.67");
	$this->setFormat("R$ligne");
	$sheet->setCellValue("S$ligne", "=(G$ligne+H$ligne-J$ligne+N$ligne)*E$ligne+((I$ligne+O$ligne)*E$ligne*1.25)+M$ligne+P$ligne+3.49*L$ligne");
	$this->setFormat("S$ligne");
	$sheet->setCellValue("V$ligne", "=(E$ligne*G$ligne)/25*(T$ligne+U$ligne)");
	$this->setFormat("V$ligne");
    }
    
    /**
     * Format de cellule
     * 
     * @param string $cells
     * @param string $format
     */
    private function setFormat($cells, $format = \PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 ){
	$sheet = $this->excel->getActiveSheet();
	$sheet->getStyle($cells)
		->getNumberFormat()
		->setFormatCode($format);
    }
    
    /**
     * Définir l'alignement Horizontal
     * 
     * @param string $cells
     * @param string $alignment
     */
    private function setTextAlignH($cells, $alignment = \PHPExcel_Style_Alignment::HORIZONTAL_CENTER){
	$sheet = $this->excel->getActiveSheet();
	$style = $sheet->getStyle($cells);
	$style->getAlignment()
		->setHorizontal($alignment);
    }
    
    /**
     * Définir l'alignement Vertical
     * 
     * @param string $cells
     * @param string $alignment
     */
    private function setTextAlignV($cells, $alignment = \PHPExcel_Style_Alignment::VERTICAL_CENTER){
	$sheet = $this->excel->getActiveSheet();
	$style = $sheet->getStyle($cells);
	$style->getAlignment()
		->setVertical($alignment);
    }
}

